﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using S.ReportForms.Domain.Dto;
using S.ReportForms.Infrastructure.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace S.ReportForms.Infrastructure.Impl
{
    public class HomeProductionWarningRepository : IHomeProductionWarningRepository
    {
        private readonly IConfiguration configuration;

        public HomeProductionWarningRepository(IConfiguration configuration)
        {
            this.configuration = configuration;
        }
        /// <summary>
        /// 获取首页生产警告的数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<HomeProductionWarningDto>> GetHomeProductionWarning()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT '待配种' AS TableName, COUNT(*) AS TotalCount
                        FROM SheepFileModel
                        WHERE Sex = 2 AND PresenceState = 2 AND ReproductiveState NOT IN (7, 8) AND
                            (
                              (ReproductiveStateDate IS NOT NULL AND DATEDIFF(day, ReproductiveStateDate, GETDATE()) > 7)
                              OR 
                              (ReproductiveStateDate IS NULL AND DATEDIFF(day, CreateDate, GETDATE()) > 7)
                            )

                        UNION ALL

                        SELECT '待首次妊检' AS TableName, COUNT(*) AS TotalCount
                        FROM MaternalMating
                        WHERE BreedState != 2

                        UNION ALL

                        SELECT '待分娩' AS TableName, COUNT(*) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE PregnancytestResult = 4

                        UNION ALL

                        SELECT '种母待断奶' AS TableName, COUNT(*) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE PregnancytestResult = 4 AND DeliveryDate IS NOT NULL AND WeaningDate IS NULL;");

                    var result = connection.Query<HomeProductionWarningDto>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
